SQL Server 2012 Integration Services Design Patterns by Andy Leonard & Matt Masson & Tim Mitchell & Jessica Moss & Michelle Ufford

Author:Andy Leonard & Matt Masson & Tim Mitchell & Jessica Moss & Michelle Ufford [Leonard, Andy & Masson, Matt & Mitchell, Tim & Moss, Jessica & Ufford, Michelle]
Language: eng
Format: epub
Tags: Enterprise Applications, Computers, Data Warehousing, Business Intelligence Tools, Database Management
ISBN: 9781430237716
Publisher: Apress
Published: 2012-09-05T04:00:00+00:00

Figure 10-8 exposes this design pattern by showing the use of expression logic to break apart a data stream into multiple outputs. In this case, we process a billing file by using comparison expressions within the conditional split transformation (see the callout) to determine whether each row is paid on time, not yet due, or past due, and then send it to the appropriate output accordingly.

Figure 10-8. Using expressions to define multiple paths

One interesting caveat regarding the application of expressions within the data flow is the way in which SSIS exposes component-level expressions. Although the expression language is very useful within the pipeline of the data flow, most components do not actually expose properties that can be set using expressions. For those that do allow expressions on certain properties, these expressions are surfaced as elements of the data flow itself, and will appear as part of the options in the Data Flow Properties window while working in the control flow.

As shown in Figure 10-9, we are using the expression properties of the data flow to access the ADO.NET data source within that data flow. As you can see, the identifier in the Property column shows that this expression belongs to the data source within the data flow, allowing us to set the SqlCommand property of that source. It’s useful to note here that I used the ADO.NET source purposefully in this example. As this source does not currently allow the use of parameters, setting the SqlCommand property is often an acceptable substitute for dynamically retrieving data from a relational database using this component.